using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Collections;


/// <summary>
/// Summary description for Reminder
/// This class is used for sending reminders
/// Utilized in Global.asxa
/// </summary>
public class Reminder
{
	public Reminder(){}

    public static Hashtable GetUsers()
    {
        Hashtable htUsers = new Hashtable();
        object connStr = ConfigurationSettings.AppSettings["sqlConn"];
        using (SqlConnection conn = new SqlConnection(connStr.ToString()))
        {
            string sqlStr = "SELECT UserID, [Email] "+
                            "FROM cms_Proposals p INNER JOIN tbl_login l ON p.[UserID]=l.[id] "+
                            "WHERE p.[Status]='Not Confirmed' AND (DATEDIFF(d, GETDATE(), ReminderDate)<1 OR DATEDIFF(d, GETDATE(), ReminderDate)=7) " +
                            "GROUP BY UserID, [Email]";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                htUsers.Add(dr[0].ToString(), dr[1].ToString());
            }
        }

        return htUsers;
    }

    public static Hashtable GetMarket()
    {
        Hashtable htMarket = new Hashtable();
        object connStr = ConfigurationSettings.AppSettings["sqlConn"];
        using (SqlConnection conn = new SqlConnection(connStr.ToString()))
        {
            // TODO change table name accordingly
            string sqlStr = "SELECT [id], [BillingAlert] FROM tbl_IRLs;";
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                htMarket.Add(dr[0].ToString(), dr[1].ToString());
            }
        }

        return htMarket;
    }

}
